﻿using System;
using System.Collections.Generic;
using System.Web;
using hdbankwebservice.DTO;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace hdbankwebservice.DAO
{
    public class SIDDAO
    {
        public static string LaySID(string SIDString)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"SELECT * FROM SID WHERE (SID = @SID)";
            List<DbParameter> listparams = new List<DbParameter>();

            DbParameter para = dl.DoiSo();
            para.ParameterName = "SID";
            para.DbType = DbType.String;
            para.Direction = ParameterDirection.Input;
            para.Value = SIDString;
            listparams.Add(para);

            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql, listparams);
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            else
            {
                SIDDTO SID = new SIDDTO();
                SID.SID = dt.Rows[0]["SID"].ToString();
                return SID.SID;
            }
        }

        public static string LayUserName(string sid)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"SELECT * FROM SID WHERE (SID = @sid)";
            List<DbParameter> listparams = new List<DbParameter>();

            DbParameter para = dl.DoiSo();
            para.ParameterName = "sid";
            para.DbType = DbType.String;
            para.Direction = ParameterDirection.Input;
            para.Value = sid;
            listparams.Add(para);

            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql, listparams);
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            else
            {
                SIDDTO SID = new SIDDTO();
                SID.UserName = dt.Rows[0]["USERNAME"].ToString();
                return SID.UserName;
            }
        }

        public static int ThemSID(string SIDString, string username)
        {
           
                SQLDataAccessHelper dl = new SQLDataAccessHelper();
                string sqlString = @"insert SID(SID,USERNAME) values('" + SIDString + "','"+ username +"')";

                SqlCommand sql = new SqlCommand(sqlString);
                int i = dl.ThucThiKhongTruyVan(sql);
                return i;
        }

        public static int XoaSID(string SIDString)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"delete from SID where SID='" + SIDString + "'";

            SqlCommand sql = new SqlCommand(sqlString);
            int i = dl.ThucThiKhongTruyVan(sql);
            return i;
        }

        public static string LaySIDUsername(string username)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"SELECT * FROM SID WHERE (USERNAME = @username)";
            List<DbParameter> listparams = new List<DbParameter>();

            DbParameter para = dl.DoiSo();
            para.ParameterName = "username";
            para.DbType = DbType.String;
            para.Direction = ParameterDirection.Input;
            para.Value = username;
            listparams.Add(para);

            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql, listparams);
            if (dt.Rows.Count == 0)
            {
                return "";
            }
            else
            {
                SIDDTO SID = new SIDDTO();
                SID.SID = dt.Rows[0]["SID"].ToString();
                return SID.SID;
            }
       
        }
        public static SIDDTO LaySIDDTO(string username)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"SELECT * FROM SID WHERE (USERNAME = @username)";
            List<DbParameter> listparams = new List<DbParameter>();

            DbParameter para = dl.DoiSo();
            para.ParameterName = "username";
            para.DbType = DbType.String;
            para.Direction = ParameterDirection.Input;
            para.Value = username;
            listparams.Add(para);

            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql, listparams);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                SIDDTO SID = new SIDDTO();
                SID.SID = dt.Rows[0]["SID"].ToString();
                SID.UserName = dt.Rows[0]["USERNAME"].ToString();
                return SID;
            }

        }
    }
}